package com.foreknow.demo24;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * jdbc的工具类
 * 使用预处理实现
 */
public class DBUtils {
    private Connection connection;
    private PreparedStatement preparedStatement;
    private ResultSet rs;
    /**
     * 连接数据库的
     */
    public Connection getConnection() throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/demodb?useUnicode=true&characterEncoding=utf8&useSSL=false", "root", "zjjlive82");
        return connection;
    }



    /**
     * 查询
     * select * from emp where ename=?
     */
    public ResultSet queryInfo(String sql,Object...values) throws SQLException {
        preparedStatement = connection.prepareStatement(sql);
        //可以将SQL中的？替换为数组中的数据
        for(int i = 0;i<values.length;i++){
            preparedStatement.setObject(i+1,values[i]);
        }
        //执行查询
        rs = preparedStatement.executeQuery();
        return rs;
    }



    /**
     * insert update delete
     */
    public boolean updateInfo(String sql,Object...values) throws SQLException {
        preparedStatement = connection.prepareStatement(sql);
        //可以将SQL中的？替换为数组中的数据
        for(int i = 0;i<values.length;i++){
            preparedStatement.setObject(i+1,values[i]);
        }
        //执行DML操作
        int isRigth = preparedStatement.executeUpdate();
        return isRigth==1;
    }


    /**
     * 关闭资源的
     */
    public void close() throws SQLException {
        if(rs!=null) {
            rs.close();
        }
        if(preparedStatement!=null){
            preparedStatement.close();
        }
        if(connection!=null) {
            connection.close();
        }
    }

    public static void main(String[] args) {
        DBUtils dbUtils = new DBUtils();
        try {
            //1.调用连接数据的方法
            dbUtils.getConnection();


            //2.调用dml方法添加数据
            boolean isRigth = dbUtils.updateInfo("insert into emp(empno,ename,job) values(?,?,?)","8888","tom123","test");
            if(isRigth) {
                System.out.println("添加成功......");
            }else {
                System.out.println("添加失败......");
            }


            //3.调用查询的方法
            ResultSet rs = dbUtils.queryInfo("select * from emp");
            List<Emp> list = new ArrayList<>();
            while (rs.next()) {
                Emp emp = new Emp();
                emp.setEmpno(rs.getInt("empno"));
                emp.setEname(rs.getString("ename"));
                emp.setJob(rs.getString("job"));
                list.add(emp);
            }
            //遍历集合
            for (Emp emp : list) {
                System.out.println(emp.getEmpno() + "---" + emp.getEname() + "---" + emp.getJob());
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            try {
                //关闭资源
                dbUtils.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }


}